package sz.com.postoffice.jxyzwgcase.controller;

import com.alibaba.fastjson.JSONObject;
import com.github.drinkjava2.jdbpro.JDBPRO;
import com.github.drinkjava2.jsqlbox.DbContext;
import io.swagger.annotations.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.bind.annotation.*;
import sz.com.postoffice.jxyzwgcase.core.TableDataInfo;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.*;

@Api(value = "打卡列表", tags = "{ 上班下班打卡明细列表 }")
@RestController
@RequestMapping(value = "/customer")
@Slf4j
public class ClockController {

    @GetMapping(value = "/exportedClock")
    @ApiOperation(value = "导出打卡信息", notes = "导出打卡信息")
    public void exportedClock(HttpServletResponse response, String dateTime, String code, String startTime, String endTime) {
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        // easyexcel工具类实现Excel文件导出(下文给出ExcelUtil的代码)
        //=============================2021-04-12 重构时间范围 ======================================//
        DbContext dbContext = DbContext.getGlobalDbContext();
        List<Map<String, Object>> valMapList = new ArrayList<>();
        String selectEmpSelect = "select d.`ado_id` FROM `dcp_business_5d572bf5-8183-4131-8b5c-afbcf992a268`.`base_jxyz_emp_grid_t` g  " +
                "   left join `dcp_business_5d572bf5-8183-4131-8b5c-afbcf992a268`.`dwr_jxyz_emp_d` d on g.emp_code = d.emp_code " +
                "   where d.ado_id is not null and d.emp_status = '01'  and (g.province_code = ? or g.city_code = ?  or g.county_code = ? or g.dept_code = ? )";
        if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime) && StringUtils.isNotBlank(code)) {
            String recordSql = "select  `id`, `user_id`, `clock_time`, `longitude`, `latitude`, `address`, `clock_type`, `key_point_code`, `biz_content`, `biz_id` " +
                    "from `app_signin_5d572bf5-8183-4131-8b5c-afbcf992a268`.`signin_record_t` where user_id in (" + selectEmpSelect + ")" +
                    "and DATE_ADD(clock_time, INTERVAL 8 hour) between  ? and ?  ORDER BY clock_time asc  ";
            List<Map<String, Object>> recordList = dbContext.iQueryForMapList(recordSql, JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(getStartDate(startTime)), JDBPRO.param(getFinallyDate(endTime)));

            String empInfoSql = "select i.ado_id,i.`emp_code`,i.`emp_name`,i.`emp_dept_code`,i.`emp_dept_name`,g.county_code,g.county_name,g.`city_code`,g.`city_name` " +
                    " from  `dcp_business_5d572bf5-8183-4131-8b5c-afbcf992a268`.`dwr_jxyz_emp_d` i " +
                    "left join `dcp_business_5d572bf5-8183-4131-8b5c-afbcf992a268`.`base_jxyz_emp_grid_t` g on i.emp_dept_code = g.dept_code  " +
                    "where ado_id  in ( " + selectEmpSelect + ");";
            List<Map<String, Object>> empInfoList = dbContext.iQueryForMapList(empInfoSql, JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code));
            Map<String, Map<String, Object>> empInfoMap = new HashMap<>();
            for (Map<String, Object> empInfo : empInfoList) {
                String adoId = empInfo.get("ado_id").toString();
                empInfoMap.put(adoId, empInfo);
            }
            for (Map<String, Object> recordMap : recordList) {
                String userId = recordMap.get("user_id").toString();
                Map<String, Object> infoMap = empInfoMap.get(userId);
                Set<String> infoMapKey = infoMap.keySet();
                for (String key : infoMapKey) {
                    recordMap.put(key, infoMap.get(key));
                }
                valMapList.add(recordMap);
            }
        }

        String fileName = "打卡记录";
        // 创建excel
        HSSFWorkbook wb = new HSSFWorkbook();
        try {
            // 创建一张工作表
            HSSFSheet sheet = wb.createSheet("sheet1");
            // 设置单元格宽度
            sheet.setColumnWidth(0, "列名".getBytes().length * 2 * 256);
            sheet.setColumnWidth(1, "列名".getBytes().length * 2 * 256);
            sheet.setColumnWidth(2, "列名".getBytes().length * 2 * 256);
            sheet.setColumnWidth(3, "列名".getBytes().length * 2 * 256);
            sheet.setColumnWidth(4, "列名".getBytes().length * 2 * 256);
            sheet.setColumnWidth(5, "列名".getBytes().length * 2 * 256);
            sheet.setColumnWidth(6, "列名".getBytes().length * 2 * 256);
            sheet.setColumnWidth(7, "列名".getBytes().length * 2 * 256);
            sheet.setColumnWidth(8, "列名".getBytes().length * 2 * 256);
            // 创建第一行
            HSSFRow row = sheet.createRow(0);
            // 创建第一行的列并向单元格写值
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("州市");
            cell = row.createCell(1);
            cell.setCellValue("区县");
            cell = row.createCell(2);
            cell.setCellValue("营业部");
            cell = row.createCell(3);
            cell.setCellValue("工号");
            cell = row.createCell(4);
            cell.setCellValue("姓名");
            cell = row.createCell(5);
            cell.setCellValue("日期");
            cell = row.createCell(6);
            cell.setCellValue("打卡时间");
            cell = row.createCell(7);
            cell.setCellValue("打卡地点");
            cell = row.createCell(8);
            cell.setCellValue("打卡类型");

            // 写入数据
            for (short i = 0; i < valMapList.size(); i++) {
                row = sheet.createRow(i + 1);
                row.createCell(0).setCellValue((String) valMapList.get(i).get("city_name"));
                row.createCell(1).setCellValue((String) valMapList.get(i).get("county_name"));
                row.createCell(2).setCellValue((String) valMapList.get(i).get("emp_dept_name"));
                row.createCell(3).setCellValue((String) valMapList.get(i).get("emp_code"));
                row.createCell(4).setCellValue((String) valMapList.get(i).get("emp_name"));
                row.createCell(5).setCellValue(formatDateDay((Date) valMapList.get(i).get("clock_time")));
                row.createCell(6).setCellValue(formatDate((Date) valMapList.get(i).get("clock_time")));
                row.createCell(7).setCellValue((String) valMapList.get(i).get("address"));
                row.createCell(8).setCellValue(Integer.valueOf((String) valMapList.get(i).get("clock_type")) == 1 ? "打卡" : "签到");
            }
            wb.write(getOutputStream(fileName, response));

        } catch (Exception e) {
            log.error("导出excel表格失败!", e);
        } finally {
            try {
                wb.close();
            } catch (IOException e) {
                log.error("导出excel表格失败!", e);
            }
        }
    }

    /**
     * 导出文件时为Writer生成OutputStream.
     *
     * @param fileName 文件名
     * @param response response
     * @return ""
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws IOException {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "no-store");
        response.addHeader("Cache-Control", "max-age=0");
        return response.getOutputStream();
    }


    @ApiOperation(value = "查询详细", notes = "查询详细")
    @PostMapping(value = "/queryClock")
    public TableDataInfo queryClock(@RequestBody JSONObject jsonObject) {
        DbContext dbContext = DbContext.getGlobalDbContext();
        String userId = (String) jsonObject.get("user_id");
        String dateTime = (String) jsonObject.get("date");
        String recordSql = "select  `id`, `user_id`, `clock_time`, `longitude`, `latitude`, `address`, `clock_type`, `key_point_code`, `biz_content`, `biz_id` " +
                "from `app_signin_5d572bf5-8183-4131-8b5c-afbcf992a268`.`signin_record_t` where user_id = ? " +
                "and DATE_ADD(clock_time, INTERVAL 8 hour) between  ? and ? ";
        List<Map<String, Object>> mapList = dbContext.iQueryForMapList(recordSql, JDBPRO.param(userId), JDBPRO.param(getStartDate(dateTime)), JDBPRO.param(getFinallyDate(dateTime)));
        String queryName = "select user_name from `app_signin_5d572bf5-8183-4131-8b5c-afbcf992a268`.signin_statistics_t " +
                " where  user_id =? ORDER BY date desc limit 1 ;";
        String name = dbContext.iQueryForString(queryName, JDBPRO.param(userId));
        for (Map<String, Object> map : mapList) {
            map.put("user_name", name);
            Date date = (Date) map.get("clock_time");
            Calendar cal = Calendar.getInstance();
            cal.setTime(date);
            cal.add(Calendar.HOUR, 8);// 24小时制
            date = cal.getTime();
            map.put("clock_date_time", date);
        }
        return getDataTable(mapList, (long) mapList.size());
    }

    @ApiOperation(value = "根据条件查询列表", notes = "根据条件查询列表")
    @PostMapping(value = "/getClockList")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "pageSize", value = "每页显示条数", dataType = "int", paramType = "path", required = true),
            @ApiImplicitParam(name = "pageNum", value = "当前页", dataType = "int", paramType = "path", required = true)
    })
    public TableDataInfo getClockList(@RequestBody JSONObject jsonObject) {

        Integer pageSize = (Integer) jsonObject.get("pageSize");
        Integer pageNum = (Integer) jsonObject.get("pageNum");
        String code = (String) jsonObject.get("code");
        String dateTime = (String) jsonObject.get("dateTime");
        String startTime = (String) jsonObject.get("startTime");
        String endTime = (String) jsonObject.get("endTime");

        if (StringUtils.isNotBlank(dateTime) && StringUtils.isBlank(startTime)) {
            return getClockList(pageSize, pageNum, code, dateTime);
        }
        if (StringUtils.isNotBlank(startTime)) {
            return getClockListStartTimeAndEndTime(pageSize, pageNum, code, startTime, endTime);
        }
        return getDataTable(null, 0L);
    }

    private TableDataInfo getClockList(Integer pageSize, Integer pageNum, String code, String dateTime) {
        DbContext dbContext = DbContext.getGlobalDbContext();
        String selectEmpSelect = "select d.`ado_id` FROM `dcp_business_5d572bf5-8183-4131-8b5c-afbcf992a268`.`base_jxyz_emp_grid_t` g  " +
                "   left join `dcp_business_5d572bf5-8183-4131-8b5c-afbcf992a268`.`dwr_jxyz_emp_d` d on g.emp_code = d.emp_code " +
                "   where d.ado_id is not null and d.emp_status = '01'  and (g.province_code = ? or g.city_code = ?  or g.county_code = ? or g.dept_code = ? )";

        String selectSql = "select `id`, `user_id`, `user_name`, `fense_id`, `area_code`, `count`, `in_work_status`, `off_work_status`, `is_normal`, `clock_type`, `key_point_code`, `date`" +
                " from `app_signin_5d572bf5-8183-4131-8b5c-afbcf992a268`.signin_statistics_t where 1=1 ";
        String countSql = "select count(0)" +
                " from `app_signin_5d572bf5-8183-4131-8b5c-afbcf992a268`.signin_statistics_t where 1=1 ";

        String whereSql = " and  date = ?  and user_id in (" + selectEmpSelect + ")";
        selectSql += whereSql;
        countSql += whereSql;
        String pageSql = dbContext.pagin(pageNum, pageSize, selectSql);
        List<Map<String, Object>> mapList = dbContext.iQueryForMapList(pageSql, JDBPRO.param(dateTime), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code));
        long count = dbContext.iQueryForLongValue(countSql, JDBPRO.param(dateTime), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code));

        String empInfoSql = "select `emp_code`,`emp_dept_code`,`emp_dept_name`  from  `dcp_business_5d572bf5-8183-4131-8b5c-afbcf992a268`.`dwr_jxyz_emp_d` " +
                "where ado_id  = ? ";

        for (Map<String, Object> map : mapList) {
            String adoId = (String) map.get("user_id");
            List<Map<String, Object>> empInfoMapList = dbContext.iQueryForMapList(empInfoSql, JDBPRO.param(adoId));
            if (!empInfoMapList.isEmpty()) {
                Map<String, Object> empInfoMap = empInfoMapList.get(0);
                map.put("emp_code", empInfoMap.get("emp_code"));
                map.put("emp_dept_name", empInfoMap.get("emp_dept_name"));
                map.put("emp_dept_code", empInfoMap.get("emp_dept_code"));
            }
            map.put("date", dateTime);
        }
        return getDataTable(mapList, count);
    }


    private TableDataInfo getClockListStartTimeAndEndTime(Integer pageSize, Integer pageNum, String code, String startTime, String endTime) {
        DbContext dbContext = DbContext.getGlobalDbContext();
        List<Map<String, Object>> mapList;
        long count;
        String selectEmpSelect = "select d.`ado_id` FROM `dcp_business_5d572bf5-8183-4131-8b5c-afbcf992a268`.`base_jxyz_emp_grid_t` g  " +
                "   left join `dcp_business_5d572bf5-8183-4131-8b5c-afbcf992a268`.`dwr_jxyz_emp_d` d on g.emp_code = d.emp_code " +
                "   where d.ado_id is not null and d.emp_status = '01'  and (g.province_code = ? or g.city_code = ?  or g.county_code = ? or g.dept_code = ? )";

        String selectSql = "select `id`, `user_id`, `user_name`, `fense_id`, `area_code`, `count`, `in_work_status`, `off_work_status`, `is_normal`, `clock_type`, `key_point_code`, `date`" +
                " from `app_signin_5d572bf5-8183-4131-8b5c-afbcf992a268`.signin_statistics_t where 1=1 ";
        String countSql = "select count(0)" +
                " from `app_signin_5d572bf5-8183-4131-8b5c-afbcf992a268`.signin_statistics_t where 1=1 ";

        String whereSql = " AND date BETWEEN  ? and ? and user_id in (" + selectEmpSelect + ")";
        selectSql += whereSql;
        countSql += whereSql;
        String pageSql = dbContext.pagin(pageNum, pageSize, selectSql);
        mapList = dbContext.iQueryForMapList(pageSql, JDBPRO.param(startTime), JDBPRO.param(endTime), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code));
        count = dbContext.iQueryForLongValue(countSql, JDBPRO.param(startTime), JDBPRO.param(endTime), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code));

        String empInfoSql = "select `emp_code`,`emp_dept_code`,`emp_dept_name`  from  `dcp_business_5d572bf5-8183-4131-8b5c-afbcf992a268`.`dwr_jxyz_emp_d` " +
                "where ado_id  = ? ";
        for (Map<String, Object> map : mapList) {
            String adoId = (String) map.get("user_id");
            List<Map<String, Object>> empInfoMapList = dbContext.iQueryForMapList(empInfoSql, JDBPRO.param(adoId));
            if (!empInfoMapList.isEmpty()) {
                Map<String, Object> empInfoMap = empInfoMapList.get(0);
                map.put("emp_dept_code", empInfoMap.get("emp_dept_code"));
                map.put("emp_code", empInfoMap.get("emp_code"));
                map.put("emp_dept_name", empInfoMap.get("emp_dept_name"));
            }
            Date date = (Date) map.get("date");
            Calendar c = Calendar.getInstance();
            c.setTime(date);
            c.add(Calendar.DAY_OF_MONTH, 1);

            date = c.getTime();
            Format f = new SimpleDateFormat("yyyy-MM-dd");
            map.put("date",f.format(date));
        }
        return getDataTable(mapList, count);
    }


    @SuppressWarnings({"rawtypes"})
    private TableDataInfo getDataTable(List<?> list, Long total) {
        TableDataInfo rspData = new TableDataInfo();
        rspData.setCode(200);
        rspData.setRows(list);
        if (list == null) {
            rspData.setTotal(0);
        } else {
            rspData.setTotal(total);
        }
        rspData.setMsg("请求成功");
        return rspData;
    }

    private static String formatDate(Date date) {
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Calendar ca = Calendar.getInstance();
        ca.setTime(date);
        ca.add(Calendar.HOUR_OF_DAY, 16);
        return formatter.format(ca.getTime());
    }

    private static String formatDateDay(Date date) {
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        Calendar ca = Calendar.getInstance();
        ca.setTime(date);
        ca.add(Calendar.HOUR_OF_DAY, 16);
        return formatter.format(ca.getTime());
    }


    /**
     * 得到指定日期的一天的的最后时刻23:59:59
     */
    private static String getFinallyDate(String date) {
        date += " 23:59:59";
        return date;
    }

    /**
     * 得到指定日期的一天的开始时刻00:00:00
     */
    private static String getStartDate(String date) {
        date += " 00:00:00";
        return date;
    }

    //============================  2021-04-08 重构 重构为时间范围查询==================================//
//    @ApiOperation(value = "根据条件查询列表", notes = "根据条件查询列表")
//    @PostMapping(value = "/getClockList")
//    @ApiImplicitParams({
//            @ApiImplicitParam(name = "pageSize", value = "每页显示条数", dataType = "int", paramType = "path", required = true),
//            @ApiImplicitParam(name = "pageNum", value = "当前页", dataType = "int", paramType = "path", required = true)
//    })
//    public TableDataInfo getClockList(@RequestBody JSONObject jsonObject) {
//        DbContext dbContext = DbContext.getGlobalDbContext();
//        Integer pageSize = (Integer) jsonObject.get("pageSize");
//        Integer pageNum = (Integer) jsonObject.get("pageNum");
//        String code = (String) jsonObject.get("code");
//        String dateTime = (String) jsonObject.get("dateTime");
//
//        String selectEmpSelect = "select d.`ado_id` FROM `dcp_business_5d572bf5-8183-4131-8b5c-afbcf992a268`.`base_jxyz_emp_grid_t` g  " +
//                "   left join `dcp_business_5d572bf5-8183-4131-8b5c-afbcf992a268`.`dwr_jxyz_emp_d` d on g.emp_code = d.emp_code " +
//                "   where d.ado_id is not null and d.emp_status = '01'  and (g.province_code = ? or g.city_code = ?  or g.county_code = ? or g.dept_code = ? )";
//
//        String selectSql = "select `id`, `user_id`, `user_name`, `fense_id`, `area_code`, `count`, `in_work_status`, `off_work_status`, `is_normal`, `clock_type`, `key_point_code`, `date`" +
//                " from `app_signin_5d572bf5-8183-4131-8b5c-afbcf992a268`.signin_statistics_t where 1=1 ";
//        String countSql = "select count(0)" +
//                " from `app_signin_5d572bf5-8183-4131-8b5c-afbcf992a268`.signin_statistics_t where 1=1 ";
//        List<Map<String, Object>> mapList = new ArrayList<>();
//        long count = 0L;
//        if (StringUtils.isNotBlank(dateTime) && StringUtils.isNotBlank(code)) {
//            String whereSql = " and  date = ?  and user_id in (" + selectEmpSelect + ")";
//            selectSql += whereSql;
//            countSql += whereSql;
//            String pageSql = dbContext.pagin(pageNum, pageSize, selectSql);
//            mapList = dbContext.iQueryForMapList(pageSql, JDBPRO.param(dateTime), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code));
//            count = dbContext.iQueryForLongValue(countSql, JDBPRO.param(dateTime), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code));
//        }
//        if (StringUtils.isNotBlank(dateTime) && StringUtils.isBlank(code)) {
//            String whereSql = " and  date = ?  ";
//            selectSql += whereSql;
//            countSql += whereSql;
//            String pageSql = dbContext.pagin(pageNum, pageSize, selectSql);
//            mapList = dbContext.iQueryForMapList(pageSql, JDBPRO.param(dateTime));
//            count = dbContext.iQueryForLongValue(countSql, JDBPRO.param(dateTime));
//        }
//        if (StringUtils.isBlank(dateTime) && StringUtils.isNotBlank(code)) {
//            String whereSql = "and user_id in (" + selectEmpSelect + ")";
//            selectSql += whereSql;
//            countSql += whereSql;
//            String pageSql = dbContext.pagin(pageNum, pageSize, selectSql);
//            mapList = dbContext.iQueryForMapList(pageSql, JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code));
//            count = dbContext.iQueryForLongValue(countSql, JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code), JDBPRO.param(code));
//        }
//        if (StringUtils.isBlank(dateTime) && StringUtils.isBlank(code)) {
//            String whereSql = " ";
//            selectSql += whereSql;
//            countSql += whereSql;
//            String pageSql = dbContext.pagin(pageNum, pageSize, selectSql);
//            mapList = dbContext.iQueryForMapList(pageSql);
//            count = dbContext.iQueryForLongValue(countSql);
//        }
//
//        String empInfoSql = "select `emp_code`,`emp_dept_code`,`emp_dept_name`  from  `dcp_business_5d572bf5-8183-4131-8b5c-afbcf992a268`.`dwr_jxyz_emp_d` " +
//                "where ado_id  = ? ";
//
//        for (Map<String, Object> map : mapList) {
//            String adoId = (String) map.get("user_id");
//            List<Map<String, Object>> empInfoMapList = dbContext.iQueryForMapList(empInfoSql, JDBPRO.param(adoId));
//            if (!empInfoMapList.isEmpty()) {
//                Map<String, Object> empInfoMap = empInfoMapList.get(0);
//                map.put("emp_code", empInfoMap.get("emp_code"));
//                map.put("emp_dept_code", empInfoMap.get("emp_dept_code"));
//                map.put("emp_dept_name", empInfoMap.get("emp_dept_name"));
//            }
////            map.put("date", dateTime);
//        }
//        return getDataTable(mapList, count);
//    }
}
